﻿using MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
    public class TruckTeamDAL
    {
        #region 根据车队编号和车辆名称查询车队信息
        /// <summary>
        /// 根据车队编号和车辆名称查询车队信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetTruckList()
        {
            string sql = "SELECT TeamID,TeamName FROM dbo.TruckTeam";
            return DBhelper.SqlDataAdapter(sql);
        }
        #endregion

        #region 查询车队信息
        /// <summary>
        /// 查询车队信息
        /// </summary>
        /// <param name="TeamID"></param>
        /// <param name="Leader"></param>
        /// <returns></returns>
        public DataTable GetTruckTeamList(int TeamID, string Leader, int IsDelete)
        {
            string sql = @"SELECT TeamID,TeamName,Leader,CheckInTime,case IsDelete
when 0 then '否'
else '是' end as IsDelete, AlterTime,Remark FROM dbo.TruckTeam";

            List<string> listWhere = new List<string>();
            List<SqlParameter> listPamter = new List<SqlParameter>();


            //车队名称
            if (TeamID != 0)
            {
                listWhere.Add("TruckTeam.TeamID=@TeamID");
                listPamter.Add(new SqlParameter("@TeamID", TeamID));
            }
            //车队负责人
            if (!string.IsNullOrWhiteSpace(Leader))
            {
                listWhere.Add("TruckTeam.Leader=@Leader");
                listPamter.Add(new SqlParameter("@Leader", Leader));
            }
            if (IsDelete >= 0)
            {
                listWhere.Add("IsDelete=@IsDelete");
                listPamter.Add(new SqlParameter("@IsDelete", IsDelete));
            }
            //判断是否有条件
            if (listPamter.Count > 0)
            {
                sql += " where " + string.Join(" and ", listWhere.ToArray());
            }
            return DBhelper.SqlDataAdapter(sql, listPamter.ToArray());
        }
        #endregion

        #region 修改车队信息
        /// <summary>
        /// 修改车队信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int UpdateTruckTeam(TruckTeamMODEL s)
        {
            string update = @"UPDATE TruckTeam SET TeamName=@TeamName,Leader=@Leader,
               Remark=@Remark,AlterTime=@AlterTime WHERE TeamID=@TeamID";

            SqlParameter[] parm = {
                new SqlParameter("@TeamID",s.TeamID),
                new SqlParameter("@TeamName",s.TeamName),
                new SqlParameter("@Leader",s.Leader),
                new SqlParameter("@Remark",s.Remark),
                new SqlParameter("@IsDelete",s.IsDelete),
                new SqlParameter("@AlterTime",s.AlterTime)

            };
            return DBhelper.ExecuteNonQuery(update, parm);
        }
        #endregion

        #region 根据ID获取编辑车队信息
        /// <summary>
        /// 根据ID获取编辑车队信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public TruckTeamMODEL GetUpdateTruckTeamDAL(int teamid)
        {
            string sql = @"SELECT * FROM dbo.TruckTeam WHERE TeamID=@TeamID";
            //参数
            SqlParameter[] pams = {
                new SqlParameter("@TeamID",teamid)

            };

            //连线式查询，调用DBhelper类中方法
            using (SqlDataReader reader = DBhelper.ExecuteReader(sql, pams))
            {
                //如果游标有数据行，说明根据账户名和密码查询到了数据，登录成功
                if (reader.HasRows)
                {
                    //登录成功后，执行下一条记录，获取登录用户的数据
                    if (reader.Read())
                    {
                        TruckTeamMODEL user = new TruckTeamMODEL();
                        user.TeamID = Convert.ToInt32(reader["TeamID"]);
                        user.TeamName = reader["TeamName"].ToString();

                        user.Leader = reader["Leader"].ToString();
                        user.Remark = reader["Remark"].ToString();

                        return user;
                    }
                }
                return null;
            }
        }
        #endregion

        #region 删除信息，车队信息进行修改式删除
        /// <summary>
        /// 删除信息，车队信息进行修改式删除
        /// </summary>
        /// <param name="delete"></param>
        /// <returns></returns>
        public int DeleteTruckTeam(int id)
        {
            //string sql = "delete from  dbo.[TruckTeam] where TeamID=@TeamID";
            string sql = "update dbo.[TruckTeam] set IsDelete=1 where TeamID=@TeamID";
            SqlParameter[] parm = {
                new SqlParameter("@TeamID",id)
            };
            return DBhelper.ExecuteNonQuery(sql, parm);
        }
        #endregion

        #region  删除车队信息根据id
        /// <summary>
        /// 删除车队信息根据id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TruckTeamMODEL GetDeleteList(int id)
        {
            string sql = "select * from TruckTeam where TeamID=@TeamID";
            SqlParameter[] parm = {
                new SqlParameter("@TeamID",id)
            };

            //连线式查询，调用DBhelper类中方法
            using (SqlDataReader reader = DBhelper.ExecuteReader(sql, parm))
            {
                //如果游标有数据行，说明根据账户名和密码查询到了数据，登录成功
                if (reader.HasRows)
                {
                    //登录成功后，执行下一条记录，获取登录用户的数据
                    if (reader.Read())
                    {
                        TruckTeamMODEL team = new TruckTeamMODEL();
                        team.TeamID = Convert.ToInt32(reader["TeamID"]);
                        team.TeamName = reader["TeamName"].ToString();
                        team.Leader = reader["Leader"].ToString();
                        team.Remark = reader["Remark"].ToString();
                        team.IsDelete = Convert.ToBoolean(reader["IsDelete"]);
                        return team;
                    }
                }
                return null;
            }
        }
        #endregion

        #region 添加车队信息
        /// <summary>
        /// 添加车队信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int UInsertTruckTeam(TruckTeamMODEL sql)
        {
            string inser = @"insert into dbo.[TruckTeam](TeamName,Leader,Remark,CheckInTime,IsDelete,AlterTime)
 values(@TeamName,@Leader,@Remark,@CheckInTime,@IsDelete, @AlterTime)";
            SqlParameter[] parm ={
                new SqlParameter("@TeamName",sql.TeamName),
                new SqlParameter("@Leader",sql.Leader),
                new SqlParameter("@Remark",sql.Remark),
                new SqlParameter("@CheckInTime",DateTime.Now),
                new SqlParameter("@IsDelete",false),
                new SqlParameter("@AlterTime",DateTime.Now)

            };
            return DBhelper.ExecuteNonQuery(inser, parm);
        }
        #endregion
    }
}
